﻿using Aspose.Cells;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Xml.Linq;

namespace ExcelImport.Entity
{
    public class DataVerification
    {
        public static VerificationModel verification(IEnumerable<XElement> columns, string tableName, Row dataRow, string fitler)
        {

            //1.得到单元格最终值
            MsgModel value = null;
            foreach (var col in columns)
            {
                value = ExcelImportHelp.GetColFinallyValue(dataRow, col);
                if (value.Status == true)
                {
                    //必填验证
                    VerificationModel requireResult = Required(columns, dataRow);
                    if (requireResult.IsSuccess == false)
                    {
                        return requireResult;
                    }
                    //类型格式验证
                    VerificationModel dataTypeResult = DataType(value.Value, dataRow, col);
                    if (dataTypeResult.IsSuccess == false)
                    {
                        return dataTypeResult;
                    }
                    //正则表达验证输入格式
                    VerificationModel RegExpResult = RegExp(value.Value, dataRow, col);
                    if (RegExpResult.IsSuccess == false)
                    {
                        return RegExpResult;
                    }
                    //唯一性验证
                    VerificationModel IsUniqueResult = IsUnique(columns, tableName, dataRow, fitler);
                    if (IsUniqueResult.IsSuccess == false)
                    {
                        return IsUniqueResult;
                    }

                }
                else
                {
                    return new VerificationModel()
                    {
                        IsSuccess = false,
                        Msg = value.Msg
                    };//暂不处理
                }
            }
            if (columns.Count() > 0)
            {
                return new VerificationModel()
                {
                    IsSuccess = true,
                    Msg = "验证成功"
                };
            }
            else
            {
                return new VerificationModel()
                {
                    IsSuccess = false,
                    Msg = "请添加配置列"
                };
            }


        }
        public static VerificationModel Required(IEnumerable<XElement> columns, Row dataRow)
        {
            string required;
            string text = null;
            string tips = null;
            bool result = false;
            //配置中的列名，在表头中找
            foreach (var item in columns)
            {
                required = (string)item.Attribute("Required");
                if (required == "true")
                {
                    text = (string)item.Attribute("HeaderText");
                    MsgModel msg = ExcelImportHelp.GetColFinallyValue(dataRow, item);
                    if (msg.Value == "")
                    {
                        tips += "《" + text + "》";
                        result = true;
                    }
                    //如果最终值为null说明代码表值为null
                    if (msg.Value == null)
                    {
                        return new VerificationModel()
                        {
                            IsSuccess = false,
                            Msg = msg.Msg
                        };
                    }
                }
            }

            if (result == true)
            {
                return new VerificationModel()
                {
                    IsSuccess = false,
                    Msg = "第" + dataRow.Index + "行导入失败，失败原因为：字段" + tips + "<b>字段必填</b>"
                };

            }
            else
            {
                //验证通过
                return new VerificationModel()
                {
                    IsSuccess = true,
                    Msg = "验证通过"
                };

            }
        }

        /// <summary>
        /// IsUnique 验证
        /// </summary>
        /// <param name="columns"></param>
        /// <param name="tableName"></param>
        /// <param name="dataRow"></param>
        /// <param name="fitler"> </param>
        /// <returns></returns>
        public static VerificationModel IsUnique(IEnumerable<XElement> columns, string tableName, Row dataRow, string fitler)
        {
            StringBuilder msg = new StringBuilder(); //接收某列出错
            string condition = fitler == null || fitler == "" ? "1=1" : fitler; //如果没有fitler属性或者fitler为空，则给定一个为Truet条件
            int count = 0;

            var primaryKeyColumns = columns.Where(m => (string)m.Attribute("IsUnique") == "true"); //获取属性IsUnique标识的列
            foreach (var item in primaryKeyColumns)
            {
                string text = (string)item.Attribute("HeaderText");//当前列名
                msg.Append($"《{text}》");
                condition += $"and { (string)item.Attribute("ColumnName")} = '{ ExcelImportHelp.GetColFinallyValue(dataRow, item).Value}'"; //追加条件
            }
            using (var db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sql"].ConnectionString))
            {
                string query = $"select count(1) from {tableName} where {condition}"; //查询
                count = db.GetScalar(query).ObjToInt(); //获取结果
            }
            return new VerificationModel()
            {
                IsSuccess = count == 0 || msg.ToString() == "",//数据库不存在重复，没有IsUnique标识返回true
                Msg = $"第{dataRow.Index}行导入失败，失败原因为：字段{msg.ToString()}值已存在。"
            };
        }

        public static VerificationModel DataType(string value, Row dataRow, XElement col)
        {
            //值为空，能到正则说明说明Required为false
            string Required = (string)col.Attribute("Required");
            string DataType = (string)col.Attribute("DataType");
            if (!string.IsNullOrEmpty(DataType))
            {
                bool flag = false;
                if (value == "" && Required.ToLower() == "false")
                {
                    flag = true;
                }
                else
                {
                    switch (DataType.ToLower())
                    {
                        case "datetime":
                            DateTime temp = new DateTime();
                            flag = DateTime.TryParse(value, out temp);
                            break;
                        default:
                            flag = true;
                            break;
                    }
                }
                if (flag == true)
                {
                    //验证通过
                    return new VerificationModel()
                    {
                        IsSuccess = true,
                        Msg = "验证通过"
                    };
                }
                else
                {
                    return new VerificationModel()
                    {
                        IsSuccess = false,
                        Msg = $@"第{dataRow.Index}行导入失败，失败原因为：《{(string)col.Attribute("HeaderText")}》字段,{(string)col.Attribute("DataTypeMsg")}"
                    };
                }
            }
            else
            {
                //验证通过
                return new VerificationModel()
                {
                    IsSuccess = true,
                    Msg = "验证通过"
                };
            }

        }
        /// <summary>
        /// 正则验证
        /// </summary>
        /// <param name="value"></param>
        /// <param name="dataRow"></param>
        /// <param name="col"></param>
        /// <returns></returns>
        public static VerificationModel RegExp(string value, Row dataRow, XElement col)
        {
            //值为空，能到正则说明说明Required为false
            string Required = (string)col.Attribute("Required");
            string RegExp = (string)col.Attribute("RegExp");
            if (!string.IsNullOrEmpty(RegExp))
            {
                Regex reg = new Regex(RegExp);
                if (reg.IsMatch(value.Trim()) || (value == "" && Required.ToLower() == "false"))
                {
                    //验证通过
                    return new VerificationModel()
                    {
                        IsSuccess = true,
                        Msg = "验证通过"
                    };
                }
                else
                {
                    return new VerificationModel()
                    {
                        IsSuccess = false,
                        Msg = $@"第{dataRow.Index}行导入失败，失败原因为：《{(string)col.Attribute("HeaderText")}》字段,{(string)col.Attribute("RegExpMsg")}"
                    };
                }
            }
            else
            {
                //验证通过
                return new VerificationModel()
                {
                    IsSuccess = true,
                    Msg = "验证通过"
                };
            }

        }


    }
}